﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;

namespace Ortodoctor.modelo
{
    public class ModeloClinica : conexao
    {
        public ModeloClinica()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Clinica c)
        {
            int id = 0;
            object obj;
            try
            {
                //con.open();
                String sql = "INSERT INTO clinica(id_endereco) " +
                             "VALUES (:p1) returning id_clinica";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", c.Endereco.IdEndereco);
                //obj = com.ExecuteScalar();
                id = (int.Parse(com.ExecuteScalar().ToString()));
            }
            finally
            {
                conn.Close();
                modeloCaixa mc = new modeloCaixa();
                mc.insert(new Caixas(0,"Entrada/Ganho", "Parcelas Pagas", "Automatica"), id);
            }
            return id;
        }

        private int getMaxId()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT Max(id_clinica) as id_clinica FROM clinica";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return Convert.ToInt32(dt.Rows[0]["id_clinica"]);
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM clinica INNER JOIN endereco USING(id_endereco)";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return dt;
        }

        public DataTable getClinica(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM clinica INNER JOIN endereco USING(id_endereco) " +
                             "WHERE id_clinica=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return dt;
        }

        public void update(Clinica c)
        {
            try
            {
                //con.open();
                String sql = "UPDATE clinica SET id_endereco=:ide, id_usuario_modificacao=:id_mod " +
                             "WHERE id_clinica=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("ide", c.Endereco.IdEndereco);
                com.Parameters.AddWithValue("id", c.IdClinica);
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO clinica_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM clinica WHERE id_clinica=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "update clinica_hist set id_usuario_modificacao = :id_mod, data_modificacao = :data WHERE id_clinica=:id";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
